package bi.uc.login;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

	public final static String DbName = "example.db";
	public final static int DbVersion = 1;
	public final String USER_TABLE_NAME = "user_table";
	public final String USERNAME = "username";
	public final String PASSWORD = "password";
	public final String ISSAVED = "issaved";
	public SQLiteDatabase db;

	public DBHelper(Context context) {
		// TODO Auto-generated constructor stub
		super(context, DbName, null, DbVersion);
		Log.d("****database****", "1111111");
		establishDb();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		try {
			db.execSQL("CREATE TABLE IF NOT EXISTS " + USER_TABLE_NAME
					+ "(UserID INTEGER PRIMARY KEY AUTOINCREMENT," + USERNAME
					+ " TEXT NOT NULL, " + PASSWORD + " TEXT, " + ISSAVED
					+ " INTEGER)");
		} catch (SQLException e) {
			Log.d("****database****", "onCreate");
			Log.e("****database****", e.getMessage());
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME);
		onCreate(db);
	}

	// 打开数据库
	private void establishDb() {

		if (this.db == null) {
			Log.d("****database****", "222222222222");
			this.db = this.getWritableDatabase();
		}
		;
	}

	// 关闭数据库
	public void cleanup() {
		if (this.db != null) {
			this.db.close();
			this.db = null;
		}
	}

	/**
	 * 插入一条记录
	 * 
	 * @param map
	 *            要插入的记录
	 * @param tableName
	 *            表名
	 * @return 插入记录的id -1表示插入不成功
	 */
	public long insertOrUpdate(String userName, String password, int isSaved) {
		boolean isUpdate = false;
		String[] usernames = queryAllUserName();
		for (int i = 0; i < usernames.length; i++) {
			if (userName.equals(usernames[i])) {
				isUpdate = true;
				break;
			}
		}
		long id = -1;
		if (isUpdate) {
			id = update(userName, password, isSaved);
		} else {
			if (this.db != null) {
				ContentValues values = new ContentValues();
				values.put(USERNAME, userName);
				values.put(PASSWORD, password);
				values.put(ISSAVED, isSaved);
				id = this.db.insert(USER_TABLE_NAME, null, values);
			}
		}
		return id;
	}

	/**
	 * 删除一条记录
	 * 
	 * @param userName
	 *            用户名
	 * @param tableName
	 *            表名
	 * @return 删除记录的id -1表示删除不成功
	 */
	public long delete(String userName) {
		long id = this.db.delete(USER_TABLE_NAME, USERNAME + " = '" + userName
				+ "'", null);
		return id;
	}

	/**
	 * 更新一条记录
	 * 
	 * @param
	 * 
	 * @param tableName
	 *            表名
	 * @return 更新过后记录的id -1表示更新不成功
	 */
	public long update(String username, String password, int isSaved) {
		ContentValues values = new ContentValues();
		values.put(USERNAME, username);
		values.put(PASSWORD, password);
		values.put(ISSAVED, isSaved);
		long id = this.db.update(USER_TABLE_NAME, values, USERNAME + " = '"
				+ username + "'", null);
		return id;
	}

	/**
	 * 根据用户名查询密码
	 * 
	 * @param username
	 *            用户名
	 * @param tableName
	 *            表名
	 * @return Hashmap 查询的记录
	 */
	public String queryPasswordByName(String username) {
		String sql = "select * from " + USER_TABLE_NAME + " where " + USERNAME
				+ " = '" + username + "'";
		Cursor cursor = this.db.rawQuery(sql, null);
		String password = "";
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			password = cursor.getString(cursor.getColumnIndex(PASSWORD));
		}
		cursor.close();
		return password;
	}

	/**
	 * 记住密码选项框是否被选中
	 * 
	 * @param username
	 * @return
	 */
	public int queryIsSavedByName(String username) {
		String sql = "select * from " + USER_TABLE_NAME + " where " + USERNAME
				+ " = '" + username + "'";
		Cursor cursor = this.db.rawQuery(sql, null);
		int isSaved = 0;
		if (cursor.getCount() > 0) {
			cursor.moveToFirst();
			isSaved = cursor.getInt(cursor.getColumnIndex(ISSAVED));
		}
		cursor.close();
		return isSaved;
	}

	/**
	 * 查询所有用户名
	 * 
	 * @param tableName
	 *            表名
	 * @return 所有记录的list集合
	 */
	public String[] queryAllUserName() {
		if (db != null) {
			Cursor cursor = this.db.query(USER_TABLE_NAME, null, null, null,
					null, null, null);
			int count = cursor.getCount();
			String[] userNames = new String[count];
			if (count > 0) {
				cursor.moveToFirst();
				for (int i = 0; i < count; i++) {
					userNames[i] = cursor.getString(cursor
							.getColumnIndex(USERNAME));
					cursor.moveToNext();
				}
			}
			cursor.close();
			return userNames;
		} else {
			return new String[0];
		}

	}

}
